'''
Created on 2020年3月3日

@author: Administrator
'''
from orm.model import MyCustomer,User, SummaryTrack,Dept
import datetime
from dateutil.relativedelta import relativedelta
from view.baseview import BaseView
from common.public import *

class StstitrackView(BaseView):
    def __init__(self):
        pass
    
    def query(self,params):
        """取得业务统计信息"""
                
        result = []
        where_condition = 'True'
        if params['dept']:
            where_condition = where_condition + " and b.dept_id in " + str(params['dept']) 
        if params['team']:
            where_condition = where_condition + " and b.leader_id in " + str(params['team']) 
        if params['personnel']:
            where_condition = where_condition + " and a.tracker_id in " + str(params['personnel']) 
        where_condition= where_condition.replace('[','(').replace(']',')')
        sql = '''
                SELECT
                    dept,
                    team,
                    trackerid,
                    name,
                    sum( total ) total,
                    sum( weixin ) weixin,
                    sum( A ) A,
                    sum( B ) B,
                    sum( C ) C,
                    sum( visited ) visited,
                    sum( signed ) signed,
                    sum( signnew ) signnew,
                    sum( signcontinue ) signcontinue,
                    sum( signold ) signold,
                    sum( amount ) amount,
                    sum( actualcollection ) actualcollection,
                    sum( balance ) balance,
                    sum( moveto ) moveto 
                FROM
                    (
                        (
                        SELECT
                            e.name dept,
                            c.username team,
                            a.tracker_id trackerid,
                            b.username name,
                            sum( total ) total,
                            sum( weixin ) weixin,
                            sum( A ) A,
                            sum( B ) B,
                            sum( C ) C,
                            sum( visited ) visited,
                            sum( signed ) signed,
                            sum( signnew ) signnew,
                            sum( signcontinue ) signcontinue,
                            sum( signold ) signold,
                            sum( amount ) amount,
                            sum( actualcollection ) actualcollection,
                            sum( balance ) balance,
                            sum( moveto ) moveto 
                        FROM
                            summarytrack a
                            LEFT JOIN USER b ON a.tracker_id = b.id
                            LEFT JOIN USER c ON b.leader_id = c.id
                            LEFT JOIN DEPT e ON c.dept_id = e.id 
                        WHERE
                        a.date >= '{0}' 
                        AND a.date < '{1}' 
                        AND {2}
                        GROUP BY
                            a.tracker_id 
                        ORDER BY
                            b.dept_id,
                            b.leader_id,
                            b.username 
                        ) UNION ALL
                        (
                        SELECT
                            e.NAME dept,
                            c.username team,
                            a.receiver_id trackerid,
                            b.username NAME,
                            0 total,
                            0 weixin,
                            0 A,
                            0 B,
                            0 C,
                            0 visited,
                            0 signed,
                            0 signnew,
                            0 signcontinue,
                            0 signold,
                            sum(amountreceivables) amountreceivables,
                            sum(amountpayin) amountpayin,
                            sum(amountend) amountend,
                            0 moveto 
                        FROM
                            transform a
                            INNER JOIN transformrecorder tr ON a.id = tr.transform_id
                            LEFT JOIN USER b ON a.receiver_id = b.id
                            LEFT JOIN USER c ON b.leader_id = c.id
                            LEFT JOIN DEPT e ON c.dept_id = e.id 
                    WHERE
                            tr.collectiondate >= '{3}' 
                            AND tr.collectiondate <= '{4}' 
                    AND {2}
                    GROUP BY
                            tr.reciver_id
                    ORDER BY
                            b.dept_id,
                            b.leader_id,
                            b.username
                        ) 
                    ) E 
                GROUP BY
                    trackerid 
                ORDER BY
                    dept,
                    team,
                    trackerid
        '''.format(params['date'][0] + ' 00:00:00',params['date'][1] + ' 23:59:59',where_condition,params['date'][0],params['date'][1])
        print(sql)
        rows = MyCustomer.raw(sql)
        for row in rows:
            memoSQL = '''
                    SELECT  memo,count(memo) count
                    FROM mycustomer 
                    WHERE tracker_id = '{0}'  
                    AND updatetime >= '{1}' 
                        AND updatetime < '{2}' 
                    AND (status = '已跟踪' OR status = '已面访' )
                    GROUP BY  memo ORDER BY memo
            '''.format(row.trackerid,params['date'][0] + ' 00:00:00',params['date'][1] + ' 23:59:59')
            memorows = MyCustomer.raw(memoSQL)
            memodata = []
            for data in memorows:
                memodata.append({"memo":data.memo,"count":data.count})
            row = {
                "dept":row.dept,
                "team":row.team,
                "trackerid":row.trackerid,
                "name":row.name,
                "total":str(row.total),
                "weixin":str(row.weixin),
                "A":str(row.A),
                "B":str(row.B),
                "C":str(row.C),
                "visited":str(row.visited),
                "signed":str(row.signed),
                "signnew":str(row.signnew),
                "signcontinue":str(row.signcontinue),
                "signold":str(row.signold),
                "amount":str(row.amount),
                "actualcollection":str(row.actualcollection),
                "balance":str(row.balance),
                "moveto":str(row.moveto),
                "memodata":memodata
            }
            result.append(row)
        sql = '''
            SELECT
                tracker_id,
                GROUP_CONCAT( balancecondtion ) value
            FROM
                mycustomer 
            WHERE
                updatetime > '{0}' 
                AND updatetime < '{1}' 
                AND (STATUS = '已签约'  or STATUS = '已流转' )
                AND balance > 0
            GROUP BY
                tracker_id
        '''.format(params['date'][0] + ' 00:00:00',params['date'][1] + ' 23:59:59',where_condition)
        print(sql)
        rows = MyCustomer.raw(sql)
        blancecondtion = {}
        for row in rows:
            blancecondtion[row.tracker_id] = row.value
        
        for row in result:
            if row["trackerid"] in blancecondtion:
                row["blancecondtion"] = blancecondtion[row["trackerid"]]
            else:
                row["blancecondtion"] = ""
        

        if result.__len__()==0:
            return super().resultSummary(210, "没有可统计的信息！", [])
        
        return super().resultSummary(200, "信息统计成功！", result)


if __name__ == '__main__':
    tv = StstitrackView()
    params = {"dept":[2],"team":[5],"personnel":[],"date":['2020-10-01','2020-10-20']}
    res = tv.query(params)
    print(res);
